package modelo.bd.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import modelo.bd.ConexionPool;
import modelo.bd.excepcion.DAOException;
import modelo.dominio.Factura;
import modelo.servicio.Proveedores;

public class FacturaDAO {

    private DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    private String query;

    public void add(Factura entity, String usuario) throws DAOException {
        Connection conexion = null;
        PreparedStatement sentencia = null;
        try {
            GenericoDAO dao = new GenericoDAO();
            String condicionMax = "fapr_prpo_numero = " + entity.getProveedor().getIdentificador();
            long folio = dao.selectMax("sil_facturas_proveedor", "fapr_numero", condicionMax);
            query = ("INSERT INTO sil_facturas_proveedor (\n"
                    + "            fapr_prpo_numero,\n"
                    + "            fapr_numero,\n"
                    + "            fapr_fluj_clave,\n"
                    + "            fapr_hifl_numero,\n"
                    + "            fapr_folio,\n"
                    + "            fapr_fecha_factura,\n"
                    + "            fapr_fecha_captura,\n"
                    + "            fapr_monto,\n"
                    + "            fapr_concepto,\n"
                    + "            fapr_comentarios,\n"
                    + "            fapr_usuario_creo,\n"
                    + "            fapr_fecha_creo) \n"
                    + "VALUES (\n"
                    + "            '" + entity.getProveedor().getIdentificador() + "', \n"
                    + "            " + folio + ", \n"//consecutivo
                    + "            '" + entity.getSolicitud().getFlujo().getClave().replace("'", "''") + "', \n"
                    + "            " + entity.getSolicitud().getFolio() + ", \n"
                    + "            '" + entity.getFolio() + "', \n"
                    + "            TO_DATE('" + df.format(entity.getFacturacion()) + "', 'dd/MM/yyyy'), \n"
                    + "            SYSDATE, \n"
                    + "            " + entity.getMonto() + ", \n"
                    + "            '" + entity.getConcepto() + "', \n"
                    + "            '" + entity.getComentarios() + "', \n"
                    + "            '" + usuario + "', \n"
                    + "            SYSDATE)");
            conexion = ConexionPool.getInstancia().getConexion();
            sentencia = conexion.prepareStatement(query);
            sentencia.executeUpdate();
        } catch (SQLException ex) {
            throw new DAOException("Error FADA-SQL " + ex.getMessage());
        } finally {
            try {
                if (sentencia != null) {
                    sentencia.close();
                }
                if (conexion != null) {
                    conexion.close();
                }
            } catch (SQLException ex) {
                throw new DAOException("Error FADA-SQL " + ex.getMessage());
            }
        }
    }

    protected List<Factura> select() throws DAOException {
        Connection conexion = null;
        PreparedStatement sentencia = null;
        ResultSet resultado = null;
        List<Factura> resultados = null;
        try {
            conexion = ConexionPool.getInstancia().getConexion();
            sentencia = conexion.prepareStatement(query);
            resultado = sentencia.executeQuery();
            resultados = new ArrayList<Factura>();
            while (resultado.next()) {
                Factura factura = new Factura();
                factura.setIdentificador(resultado.getInt("numero"));
                factura.setFolio(resultado.getString("folio"));
                factura.setConcepto(resultado.getString("concepto"));
                factura.setFacturacion(resultado.getDate("fecha_factura"));
                factura.setMonto(resultado.getDouble("monto"));
                factura.setProveedor(Proveedores.instancia().porClave(resultado.getString("numero_proveedor")));
                resultados.add(factura);
            }
        } catch (SQLException ex) {
            throw new DAOException("Error FADS-SQL " + ex.getMessage());
        } finally {
            try {
                if (resultado != null) {
                    resultado.close();
                }
                if (sentencia != null) {
                    sentencia.close();
                }
                if (conexion != null) {
                    conexion.close();
                }
            } catch (SQLException ex) {
                throw new DAOException("Error FADS-SQL " + ex.getMessage());
            }
        }
        return resultados;
    }

    public List<Factura> selectAll() throws DAOException {
        query = "";
        return select();
    }

    public List<Factura> selectWhere(String condition) throws DAOException {
        query = "SELECT fapr_numero        AS numero,\n"
                + "       fapr_folio         AS folio,\n"
                + "       fapr_fecha_factura AS fecha_factura,\n"
                + "       fapr_monto         AS monto,\n"
                + "       fapr_concepto      AS concepto,\n"
                + "       fapr_prpo_numero   AS numero_proveedor \n"
                + "FROM sil_facturas_proveedor \n"
                + "WHERE  " + condition;
        return select();
    }

    public List<Factura> selectProvR12(String proveedor) throws DAOException {
        query = "SELECT ai.invoice_id     AS numero, \n"
                + "       ai.invoice_num    AS folio, \n"
                + "       ai.invoice_date   AS fecha_factura, \n"
                + "       ai.invoice_amount AS monto, \n"
                + "       ai.description    AS concepto, \n"
                + "       pv.vendor_name    AS razon_social_prov, \n"
                + "       pv.segment1       AS numero_proveedor \n"
                + "FROM   apps.ap_invoices_all@dbl_sil_sil_ebs ai, apps.po_vendors@dbl_sil_sil_ebs pv \n"
                + "WHERE  org_id                        = 370 \n"
                + "AND    pv.vendor_id                  = ai.vendor_id \n"
                + "AND    NVL(payment_status_flag, 'N') = 'N' \n"
                + "AND    cancelled_date                IS NULL \n"
                + "AND    project_id                    IS NULL \n"
                + "AND    pv.segment1                   = " + proveedor + " \n"
                + "AND    SIL_F_MUESTRA_FACTURA(" + proveedor + ", ai.invoice_num) = 'S' \n"
                + "AND    apps.ap_invoices_pkg.get_posting_status@dbl_sil_sil_ebs (invoice_id) = 'Y' \n"
                + "ORDER BY ai.invoice_num";
        return select();
    }

    public List<Factura> selectWhereR12(String condition) throws DAOException {
        query = "SELECT ai.invoice_id     AS numero, \n"
                + "       ai.invoice_num    AS folio, \n"
                + "       ai.invoice_date   AS fecha_factura, \n"
                + "       ai.invoice_amount AS monto, \n"
                + "       ai.description    AS concepto, \n"
                + "       pv.vendor_name    AS razon_social_prov, \n"
                + "       pv.segment1       AS numero_proveedor \n"
                + "FROM   apps.ap_invoices_all@dbl_sil_sil_ebs ai, apps.po_vendors@dbl_sil_sil_ebs pv \n"
                + "WHERE  org_id                        = 370 \n"
                + "AND    pv.vendor_id                  = ai.vendor_id \n"
                + "AND    NVL(payment_status_flag, 'N') = 'N' \n"
                + "AND    cancelled_date                IS NULL \n"
                + "AND    project_id                    IS NULL \n"
                //+ "AND    pv.segment1                   = "+proveedor+" \n"
                + "AND    apps.ap_invoices_pkg.get_posting_status@dbl_sil_sil_ebs (invoice_id) = 'Y'"
                + "AND " + condition;
        return select();
    }

    public void update(Factura factura, String usuario) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    public void deleteWhere(String string) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }
}
